package com.anolesoft.twelvePlan.mapper;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.twelvePlan.dto.*;
import com.anolesoft.twelvePlan.entity.MesEqualCatalogDO;
import com.anolesoft.twelvePlan.entity.MesPlanXiaDaDO;
import com.anolesoft.twelvePlan.vo.MesOrderTraceTiaoMaVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

/**
 * @author zyq
 * @date 2022/3/20
 */
@Mapper
public interface MesPlanXiaDaMapper extends BaseMapper<MesPlanXiaDaDO> {

    // 修改标记
    @Update("update mes_plan_xiada a set " +
            " a.flag = '修改'" +
            " where a.planid = #{planId, jdbcType=VARCHAR}")
    int updateFlag(String planId);
    // 删除标记
    @Update("update mes_plan_xiada a set " +
            " a.flag = '删除'" +
            " where a.planid = #{planId, jdbcType=VARCHAR}")
    int deleteFlag(String planId);

    // 查看是否重复下达
    @Select("select count(*) from mes_plan_xiada a" +
            " where a.planId = #{mesGyInfoToPlanDTO.objuid, jdbcType=VARCHAR}" +
            " and a.checktime2 = #{mesGyInfoToPlanDTO.checktime2}" +
            " and replace(a.serialnumber, '-', '') = #{mesGyInfoToPlanDTO.serialnumber, jdbcType=VARCHAR}")
    int repeatPlan(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO);

    // 确定标记是否为新增
    @Select("select count(*) from mes_plan_xiada a" +
            " where a.checktime2 like (to_char(#{mesGyInfoToPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') || '%')" +
            " and a.jx = trim(upper(#{mesGyInfoToPlanDTO.jx, jdbcType=VARCHAR}))")
    int judgeFlag(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO);

    // 计划下达
    @Insert("insert into mes_plan_xiada(jx, parentpartid, partid, djnum, innerwork, longperiodpart, deliverytype, parttype," +
            " jfworkshop, jsworkshop, deliveryorder, matching, grouping_a, checktime, isexecutable," +
            " deliverstate, jxmlstock, mlstocktotal, bcpstocknum, bcpstocktotal, mesprogress," +
            " outprocess, equal, waixie, waixiefactory, tempplannum, remarks1, moreinfo, chargeperson," +
            " endproduct, tools, equipment, technicalquality, blanking, outsourcing, outcooperation," +
            " plantime, ladong, state, planid, checktime2, factory_id, xiadauid, flag, jzjh, lltotal, serialnumber) values" +
            " (trim(upper(#{mesGyInfoToPlanDTO.jx, jdbcType=VARCHAR})), trim(upper(#{mesGyInfoToPlanDTO.parentpartid, jdbcType=VARCHAR}))," +
            " trim(upper(#{mesGyInfoToPlanDTO.partid, jdbcType=VARCHAR})), #{mesGyInfoToPlanDTO.djnum, jdbcType=INTEGER}," +
            " #{mesGyInfoToPlanDTO.innerwork, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.longperiodpart, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.deliverytype, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.parttype, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.factoryId, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.jsworkshop, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.deliveryorder, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.matching, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.groupingA, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.checktime, jdbcType=DATE}," +
            " #{mesGyInfoToPlanDTO.isexecutable, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.deliverstate, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.jxmlstock, jdbcType=INTEGER}," +
            " #{mesGyInfoToPlanDTO.mlstocktotal, jdbcType=INTEGER}, #{mesGyInfoToPlanDTO.bcpstocknum, jdbcType=INTEGER}," +
            " #{mesGyInfoToPlanDTO.bcpstocktotal, jdbcType=INTEGER}, #{mesGyInfoToPlanDTO.mesprogress, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.outprocess, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.equal, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.waixie, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.waixiefactory, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.tempplannum, jdbcType=INTEGER}, #{mesGyInfoToPlanDTO.remarks1, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.moreinfo, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.chargeperson, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.endproduct, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.tools, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.equipment, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.technicalquality, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.blanking, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.outsourcing, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.outcooperation, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.plantime, jdbcType=DATE}," +
            " #{mesGyInfoToPlanDTO.ladong, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.state, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.objuid, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.checktime2, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.factoryId, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.xiadauid, jdbcType=VARCHAR}, #{flag, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.jzjh, jdbcType=VARCHAR}, #{mesGyInfoToPlanDTO.lltotal, jdbcType=VARCHAR}," +
            " #{mesGyInfoToPlanDTO.serialnumber})" )
    int planXiaDa1(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO, @Param("flag") String flag);

    //@Update("update mes_plan_xiada a set (a.ladongchecktime, a.plangrade, a.jxmltotal, a.jxbcptotal, a.issue, a.delivertime," +
    //        " a.completiontime, a.gdchecktime, a.productstock, a.affectedstation, a.equalflag, a.putnum, a.minpjcid, a.processroute) =" +
    //        " (select b.ladongchecktime, b.plangrade, b.jxmltotal, b.jxbcptotal, b.issue, b.delivertime, b.completiontime, b.gdchecktime," +
    //        " b.productstock, b.affectedstation, b.equalflag, b.putnum, b.minpjcid, b.processroute from mes_gy_info b " +
    //        " where a.planid = #{mesGyInfoToPlanDTO.objuid, jdbcType=VARCHAR}" +
    //        " and a.checktime2 = to_char(#{mesGyInfoToPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm-dd')")
    @Update("update mes_plan_xiada a set (a.ladongchecktime, a.plangrade, a.jxmltotal, a.jxbcptotal, a.issue, a.delivertime," +
            " a.completiontime, a.gdchecktime, a.productstock, a.affectedstation, a.equalflag, a.putnum, a.processroute," +
            " a.serialnumber, a.delivertotal) = " +
            " (select b.ladongchecktime, b.plangrade, b.jxmltotal, b.jxbcptotal, b.issue, b.delivertime, b.completiontime," +
            " b.gdchecktime, b.productstock, b.affectedstation, b.equalflag, b.putnum, b.processroute, c.serialnumber, b.delivertotal" +
            " from mes_gy_info b inner join mes_gy_info_more c on b.objuid = c.mes_gy_info_obj\n" +
            " where b.objuid = #{mesGyInfoToPlanDTO.objuid, jdbcType=VARCHAR} " +
            " and c.nianyue = to_char(#{mesGyInfoToPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm')" +
            " and replace(c.serialnumber, '-', '') = #{mesGyInfoToPlanDTO.serialnumber})" +
            " where a.planid = #{mesGyInfoToPlanDTO.objuid, jdbcType=VARCHAR}" +
            "   and a.serialnumber = #{mesGyInfoToPlanDTO.serialnumber}" +
            "   and a.checktime2 = to_char(#{mesGyInfoToPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm')")
    int planXiaDa2(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO);

    @Update("update mes_gy_info_more a set a.xiada = '1' " +
            "where a.mes_gy_info_obj = #{mesGyInfoToPlanDTO.objuid, jdbcType=VARCHAR} " +
            "and a.nianyue = to_char(#{mesGyInfoToPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm')" +
            "and replace(a.serialnumber, '-', '') = #{mesGyInfoToPlanDTO.serialnumber}")
    int planXiaDa3(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO);

    //根据机型批量下达
    @Insert("insert into mes_plan_xiada\n" +
            "              (jx, parentpartid, partid, djnum, innerwork, longperiodpart, deliverytype, parttype, jfworkshop,\n" +
            "               jsworkshop, deliveryorder, matching, grouping_a, checktime, isexecutable, delivertotal, deliverstate,\n" +
            "               jxmlstock, mlstocktotal, bcpstocknum, bcpstocktotal, mesprogress, outprocess, equal, waixie, waixiefactory,\n" +
            "               tempplannum, remarks1, moreinfo, chargeperson, endproduct, tools, equipment, technicalquality, blanking,\n" +
            "               outsourcing, outcooperation, ladong, ladongchecktime, plangrade, jxmltotal, jxbcptotal, issue, productstock,\n" +
            "               affectedstation, equalflag, putnum, planid, xiadauid, xiadatime, factory_id, processroute, serialnumber,\n" +
            "               flag, state, checktime2, jzjh, lltotal)\n" +
            "              select a.jx, a.parentpartid, a.partid, a.djnum, a.innerwork, a.longperiodpart, a.deliverytype, a.parttype,\n" +
            "                     a.factory_id as jfworkshop, a.jsworkshop, a.deliveryorder, b.matching, b.grouping_a, b.checktime,\n" +
            "                     b.isexecutable, b.delivertotal, a.deliverstate, a.jxmlstock, a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal,\n" +
            "                     a.mesprogress, a.outprocess, a.equal, a.waixie, a.waixiefactory, a.tempplannum, a.remarks1, b.moreinfo,\n" +
            "                     a.chargeperson, a.endproduct, a.tools, a.equipment, a.technicalquality, a.blanking, a.outsourcing,\n" +
            "                     a.outcooperation, a.ladong, a.ladongchecktime, a.plangrade, a.jxmltotal, a.jxbcptotal, a.issue,\n" +
            "                     a.productstock, a.affectedstation, a.equalflag, a.putnum, a.objuid, #{mesGyInfoToPlanDTO.xiadauid}, sysdate, a.factory_id,\n" +
            "                     a.processroute, b.serialnumber, #{flag}, a.state, #{mesGyInfoToPlanDTO.checktime2}, b.jzjh, a.lltotal\n" +
            "                from (select * from mes_gy_info a\n" +
            "                       where a.objuid in\n" +
            "                             (select a.objuid from mes_gy_info a\n" +
            "                               where a.jx = trim(upper(#{mesGyInfoToPlanDTO.jx}))\n" +
            "                                 and a.factory_id = #{mesGyInfoToPlanDTO.factoryId}\n" +
            "                                 and a.deleteflag = 0\n" +
            "                                 and a.objuid not in\n" +
            "                                     (select a.planid from mes_plan_xiada a\n" +
            "                                       where a.checktime2 = substr(#{mesGyInfoToPlanDTO.checktime2},1,7)))) a\n" +
            "               inner join (select b.mes_gy_info_obj, b.grouping_a, b.checktime, b.isexecutable, b.jzjh, b.serialnumber, b.matching, b.delivertotal, b.moreinfo\n" +
            "                             from mes_gy_info_more b where b.nianyue = to_char(#{mesGyInfoToPlanDTO.checkTime2Date}, 'yyyy-mm')) b\n" +
            "                  on a.objuid = b.mes_gy_info_obj")
    int xiaDaForJx(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO, @Param("flag") String flag);

    @Update("update mes_gy_info_more a set a.xiada = '1' " +
            "where a.jx = trim(upper(#{mesGyInfoToPlanDTO.jx})) " +
            "and a.factory_id = #{mesGyInfoToPlanDTO.factoryId} " +
            "and a.nianyue = to_char(#{mesGyInfoToPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') " +
            "and a.xiada = 0")
    int xiaDaForJx2(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO);

    /**
     * 计划核对查询页面机型下拉选
     * 数据源：mes_plan_xiada_jixing每月已下达机型表
     * @param planCheckDTO
     * @return
     */
    @Select("select a.jixing, a.objuid from mes_plan_xiada_jixing a " +
            "where a.factory_id = #{planCheckDTO.factoryId}" +
            "and a.nianyue = substr(#{planCheckDTO.checktime2},1,7)")
    List<MesPlanXiadaJixingDTO> selectJx(@Param("planCheckDTO") PlanCheckDTO planCheckDTO);


    /**
     * 计划核对父图号带出子件查询
     * @param planCheckDTO 查询条件
     * @param isParent 用于判断查根节点还是所有子节点
     * @return
     */
    @SelectProvider(type = createSQL5.class, method = "queryForParentpartidSQL")
    List<PlanCheckDTO> queryForParentpartid(@Param("planCheckDTO") PlanCheckDTO planCheckDTO, boolean isParent);
    public static class createSQL5 {
        public static String queryForParentpartidSQL(@Param("planCheckDTO") PlanCheckDTO planCheckDTO, boolean isParent){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();

                    if (isParent) {
                        sql.append(" a.jx, a.serialnumber from mes_plan_xiada a");
                    } else {
                        sql.append(" a.objuid, a.flag, a.jx, a.partid, a.djnum,a.innerwork, a.longperiodpart, a.processroute,");
                        sql.append(" a.deliverytype, a.parttype, a.jfworkshop, a.jsworkshop, a.deliveryorder, a.matching,");
                        sql.append(" a.grouping_a, a.checktime, a.isexecutable, a.delivertotal, a.deliverstate, a.jxmlstock,");
                        sql.append(" a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal, a.mesprogress, a.outprocess, a.equal,");
                        sql.append(" a.waixie, a.waixiefactory, a.tempplannum, a.remarks1, a.moreinfo,  a.chargeperson,");
                        sql.append(" a.endproduct, a.tools,  a.equipment, a.technicalquality, a.blanking,  a.outsourcing,");
                        sql.append(" a.outcooperation, a.plantime, a.actualtime, a.ladong, a.ladongchecktime, a.plangrade,");
                        sql.append(" a.jxmltotal, a.jxbcptotal, a.issue, a.delivertime, a.completiontime, a.gdchecktime,");
                        sql.append(" replace(a.serialnumber, '-', '') as serialnumber, a.productstock, a.affectedstation,");
                        sql.append(" a.state, a.checktime2,");
                        if (planCheckDTO.getFactoryId().equals("16")){
                            sql.append(" to_date(a.checktime2, 'yyyy-mm-dd') as checkTime2date,");
                        } else {
                            sql.append(" to_date(a.checktime2, 'yyyy-mm') as checkTime2date,");
                        }
                        sql.append(" a.jzjh, b.name,");
                        //sql.append(" c.isexecutable as isexecutable2, a.parentpartid, a.putnum, a.lltotal, a.kexing_color");
                        sql.append(" a.parentpartid, a.putnum, a.lltotal, a.kexing_color");
                        sql.append(" from mes_plan_xiada a left join (select b.mes_plan_xiada_uid,wm_concat(c.name) as name from");
                        sql.append(" (select b.mes_plan_xiada_uid, b.stf_employee_uid from mes_plan_xiada_fuzeren b) b");
                        sql.append(" inner join (select c.objuid, c.name from stf_employee c) c on b.stf_employee_uid = c.objuid");
                        sql.append(" group by b.mes_plan_xiada_uid) b on a.objuid = b.mes_plan_xiada_uid");
                    }
                    sql.append(" where a.factory_id = #{planCheckDTO.factoryId, jdbcType=VARCHAR}");
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getJx())){
                        if (isParent){
                            sql.append(" and a.jx in ( ");
                            sql.append(planCheckDTO.getJx());
                            sql.append(")");
                        } else {
                            sql.append(" and a.jx = #{planCheckDTO.jx, jdbcType=VARCHAR}");
                        }
                    }
                    if (planCheckDTO.getCheckTime2Date() != null){
                        if (planCheckDTO.getFactoryId().equals("16")){
                            if (isParent) {
                                sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm-dd') ");
                            } else {
                                sql.append(" and a.checktime2 like to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') || '%' ");
                            }
                        } else {
                            sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') ");
                        }
                        //sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getMatching())){
                        if ("空".equals(planCheckDTO.getMatching())) {
                            sql.append(" and a.matching is null ");
                        } else {
                            sql.append(" and a.matching = #{planCheckDTO.matching, jdbcType=VARCHAR} ");
                        }
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPartid())){
                        sql.append(" and a.partid = #{planCheckDTO.partid, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getInnerwork())){
                        sql.append(" and a.innerwork = #{planCheckDTO.innerwork, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPlangrade())){
                        sql.append(" and a.plangrade in ( ");
                        sql.append(planCheckDTO.getPlangrade());
                        sql.append(")");
                    }
                    //if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPlangrade())){
                    //    sql.append(" and a.plangrade = #{planCheckDTO.plangrade, jdbcType=VARCHAR} ");
                    //}
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getGroupingA())){
                        sql.append(" and a.grouping_a = #{planCheckDTO.groupingA, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getChargeperson())){
                        sql.append(" and b.name like '%' || #{planCheckDTO.chargeperson, jdbcType=VARCHAR} || '%'");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getIsexecutable())){
                        sql.append(" and a.isexecutable = #{planCheckDTO.isexecutable, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getJzjh())){
                        sql.append(" and a.jzjh = #{planCheckDTO.jzjh, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getWaixie())){
                        String a = planCheckDTO.getWaixie();
                        sql.append(" and a.waixie in ("+a+")");
                    }
                    if (planCheckDTO.getGdchecktime() != null){
                        sql.append(" and a.gdchecktime = #{planCheckDTO.gdchecktime}");
                    }
                    if (planCheckDTO.getPlantime1() != null){
                        sql.append(" and a.plantime >= to_date(to_char(#{planCheckDTO.plantime1}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (planCheckDTO.getPlantime2() != null){
                        sql.append(" and a.plantime <= to_date(to_char(#{planCheckDTO.plantime2}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getIsPlanTime())){
                        if (planCheckDTO.getIsPlanTime().equals("0")) {
                            sql.append(" and a.plantime is null");
                        }
                        if (planCheckDTO.getIsPlanTime().equals("1")) {
                            sql.append(" and a.plantime is not null");
                        }
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getParentpartid())){
                        sql.append(" and a.parentpartid = trim(upper(#{planCheckDTO.parentpartid, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPartidLike())){
                        sql.append(" and a.partid like '%'||trim(upper(#{planCheckDTO.partidLike, jdbcType=VARCHAR}))||'%' ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getMoreinfo())){
                        sql.append(" and a.moreinfo like '%'||trim(#{planCheckDTO.moreinfo, jdbcType=VARCHAR})||'%' ");
                    }
                    if (planCheckDTO.getKexingColor() != null){
                        sql.append(" and a.kexing_color = #{planCheckDTO.kexingColor, jdbcType=INTEGER} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getSerialnumber())) {
                        sql.append(" and a.serialnumber like #{planCheckDTO.serialnumber, jdbcType=VARCHAR} || '%'");
                    }
                    sql.append(" order by a.jx, a.serialnumber");
                    SELECT(sql.toString());
                    System.out.println("queryForParentpartidSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    // 计划核对查询
    @SelectProvider(type = createSQLs.class, method = "queryCheckSQL")
    List<PlanCheckDTO> queryCheck(@Param("planCheckDTO") PlanCheckDTO planCheckDTO, Page<PlanCheckDTO> page);
    public static class createSQLs {
        public static String queryCheckSQL(@Param("planCheckDTO") PlanCheckDTO planCheckDTO, Page<PlanCheckDTO> page){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();

                    sql.append(" a.objuid, a.flag, a.jx, a.partid, a.djnum,a.innerwork, a.longperiodpart, a.processroute,");
                    sql.append(" a.deliverytype, a.parttype, a.jfworkshop, a.jsworkshop, a.deliveryorder, a.matching,");
                    sql.append(" a.grouping_a, a.checktime, a.isexecutable, a.delivertotal, a.deliverstate, a.jxmlstock,");
                    sql.append(" a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal, a.mesprogress, a.outprocess, a.equal,");
                    sql.append(" a.waixie, a.waixiefactory, a.tempplannum, a.remarks1, a.moreinfo,  a.chargeperson,");
                    sql.append(" a.endproduct, a.tools,  a.equipment, a.technicalquality, a.blanking,  a.outsourcing,");
                    sql.append(" a.outcooperation, a.plantime, a.actualtime, a.ladong, a.ladongchecktime, a.plangrade,");
                    sql.append(" a.jxmltotal, a.jxbcptotal, a.issue, a.delivertime, a.completiontime, a.gdchecktime,");
                    sql.append(" replace(a.serialnumber, '-', '') as serialnumber, a.productstock, a.affectedstation,");
                    sql.append(" a.state, a.checktime2,");
                    if (planCheckDTO.getFactoryId().equals("16")){
                        sql.append(" to_date(a.checktime2, 'yyyy-mm-dd') as checkTime2date,");
                    } else {
                        sql.append(" to_date(a.checktime2, 'yyyy-mm') as checkTime2date,");
                    }
                    sql.append(" a.jzjh, b.name,");
                    //sql.append(" c.isexecutable as isexecutable2, a.parentpartid, a.putnum, a.lltotal, a.kexing_color");
                    sql.append(" a.parentpartid, a.putnum, a.lltotal, a.kexing_color");
                    sql.append(" from mes_plan_xiada a left join (select b.mes_plan_xiada_uid,wm_concat(c.name) as name from");
                    sql.append(" (select b.mes_plan_xiada_uid, b.stf_employee_uid from mes_plan_xiada_fuzeren b) b");
                    sql.append(" inner join (select c.objuid, c.name from stf_employee c) c on b.stf_employee_uid = c.objuid");
                    sql.append(" group by b.mes_plan_xiada_uid) b on a.objuid = b.mes_plan_xiada_uid");
                    //sql.append(" left join mes_gy_info_more c on a.planid = c.mes_gy_info_obj and a.serialnumber = c.serialnumber and a.checktime2 like c.nianyue || '%'");
                    sql.append(" where a.factory_id = #{planCheckDTO.factoryId, jdbcType=VARCHAR}");
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getJx())){
                        sql.append(" and a.jx in ( ");
                        sql.append(planCheckDTO.getJx());
                        sql.append(")");
                    }
                    if (planCheckDTO.getCheckTime2Date() != null){
                        if (planCheckDTO.getFactoryId().equals("16")){
                            sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm-dd') ");
                        } else {
                            sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') ");
                        }
                        //sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getMatching())){
                        if ("空".equals(planCheckDTO.getMatching())) {
                            sql.append(" and a.matching is null ");
                        } else {
                            sql.append(" and a.matching = #{planCheckDTO.matching, jdbcType=VARCHAR} ");
                        }
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPartid())){
                        sql.append(" and a.partid = #{planCheckDTO.partid, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getInnerwork())){
                        sql.append(" and a.innerwork = #{planCheckDTO.innerwork, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPlangrade())){
                        sql.append(" and a.plangrade in ( ");
                        sql.append(planCheckDTO.getPlangrade());
                        sql.append(")");
                    }
                    //if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPlangrade())){
                    //    sql.append(" and a.plangrade = #{planCheckDTO.plangrade, jdbcType=VARCHAR} ");
                    //}
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getGroupingA())){
                        sql.append(" and a.grouping_a = #{planCheckDTO.groupingA, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getChargeperson())){
                        sql.append(" and b.name like '%' || #{planCheckDTO.chargeperson, jdbcType=VARCHAR} || '%'");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getIsexecutable())){
                        sql.append(" and a.isexecutable = #{planCheckDTO.isexecutable, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getJzjh())){
                        sql.append(" and a.jzjh = #{planCheckDTO.jzjh, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getWaixie())){
                        String a = planCheckDTO.getWaixie();
                        sql.append(" and a.waixie in ("+a+")");
                    }
                    if (planCheckDTO.getGdchecktime() != null){
                        sql.append(" and a.gdchecktime = #{planCheckDTO.gdchecktime}");
                    }
                    if (planCheckDTO.getPlantime1() != null){
                        sql.append(" and a.plantime >= to_date(to_char(#{planCheckDTO.plantime1}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (planCheckDTO.getPlantime2() != null){
                        sql.append(" and a.plantime <= to_date(to_char(#{planCheckDTO.plantime2}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getIsPlanTime())){
                        if (planCheckDTO.getIsPlanTime().equals("0")) {
                            sql.append(" and a.plantime is null");
                        }
                        if (planCheckDTO.getIsPlanTime().equals("1")) {
                            sql.append(" and a.plantime is not null");
                        }
                    }
                    //if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getParentpartid())){
                    //    sql.append(" and a.parentpartid = trim(upper(#{planCheckDTO.parentpartid, jdbcType=VARCHAR})) ");
                    //}
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPartidLike())){
                        sql.append(" and a.partid like '%'||trim(upper(#{planCheckDTO.partidLike, jdbcType=VARCHAR}))||'%' ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getMoreinfo())){
                        sql.append(" and a.moreinfo like '%'||trim(#{planCheckDTO.moreinfo, jdbcType=VARCHAR})||'%' ");
                    }
                    if (planCheckDTO.getKexingColor() != null){
                        sql.append(" and a.kexing_color = #{planCheckDTO.kexingColor, jdbcType=INTEGER} ");
                    }
                    sql.append(" order by a.jx, a.serialnumber");
                    SELECT(sql.toString());
                    System.out.println("queryCheckSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    // 计划核对修改
    @UpdateProvider(type = createSQL2.class, method = "updateCheckSQL")
    int updateCheck(@Param("planCheckDTO") PlanCheckDTO planCheckDTO);
    public static class createSQL2 {
        public static String updateCheckSQL(@Param("planCheckDTO") PlanCheckDTO planCheckDTO){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" mes_plan_xiada a set ");
                    sql.append("a.partid = trim(upper(#{planCheckDTO.partid, jdbcType=VARCHAR})),");
                    sql.append("a.matching = #{planCheckDTO.matching, jdbcType=VARCHAR},");
                    sql.append(" a.delivertotal = #{planCheckDTO.delivertotal, jdbcType=VARCHAR},");
                    sql.append(" a.putnum = #{planCheckDTO.putnum, jdbcType=VARCHAR},");
                    sql.append("a.innerwork = #{planCheckDTO.innerwork, jdbcType=VARCHAR},");
                    sql.append("a.moreinfo = #{planCheckDTO.moreinfo, jdbcType=VARCHAR},");
                    sql.append("a.remarks1 = #{planCheckDTO.remarks1, jdbcType=VARCHAR},");
                    sql.append("a.blanking = #{planCheckDTO.blanking, jdbcType=VARCHAR},");
                    sql.append("a.outcooperation = #{planCheckDTO.outcooperation, jdbcType=VARCHAR},");
                    sql.append("a.outsourcing = #{planCheckDTO.outsourcing, jdbcType=VARCHAR},");
                    sql.append("a.state = #{planCheckDTO.state, jdbcType=VARCHAR},");
                    sql.append("a.endproduct = #{planCheckDTO.endproduct, jdbcType=VARCHAR},");
                    sql.append("a.waixiefactory = #{planCheckDTO.waixiefactory, jdbcType=VARCHAR},");
                    sql.append("a.waixie = #{planCheckDTO.waixie, jdbcType=VARCHAR}");
                    if (StrUtil.isEmptyOrUndefined(planCheckDTO.getPlangrade())) {
                        sql.append(", a.plangrade = ''");
                        sql.append(", a.plangradeid = ''");
                    } else if (!planCheckDTO.getPlangrade().equals("noChange")) {
                        sql.append(", a.plangrade = #{planCheckDTO.plangrade, jdbcType=VARCHAR}");
                        sql.append(", a.plangradeid = #{planCheckDTO.plangradeid, jdbcType=VARCHAR}");
                    }
                    //if (planCheckDTO.getChecktime() != null) {
                    //    sql.append(", a.checktime = #{planCheckDTO.checktime}");
                    //}
                    //if (planCheckDTO.getPlantime() != null){
                        sql.append(", a.plantime = #{planCheckDTO.plantime}");
                    //}
                    //if (planCheckDTO.getActualtime() != null){
                        sql.append(", a.actualtime = #{planCheckDTO.actualtime}");
                    //}
                    //if (planCheckDTO.getDelivertime() != null){
                        sql.append(", a.delivertime = #{planCheckDTO.delivertime}");
                    //}
                    //if (planCheckDTO.getCompletiontime() != null){
                        sql.append(", a.completiontime = #{planCheckDTO.completiontime}");
                    //}
                    //if (planCheckDTO.getGdchecktime() != null){
                        sql.append(", a.gdchecktime = #{planCheckDTO.gdchecktime}");
                    //}
                    sql.append(" where a.objuid = #{planCheckDTO.objuid, jdbcType=VARCHAR}");
                    //sql.append(" mes_plan_xiada a set ");
                    //sql.append("a.jx = trim(upper(#{planCheckDTO.jx, jdbcType=VARCHAR})),");
                    //sql.append("a.partid = trim(upper(#{planCheckDTO.partId, jdbcType=VARCHAR})),");
                    //sql.append("a.djnum = #{planCheckDTO.djNum, jdbcType=VARCHAR},");
                    //sql.append("a.innerwork = #{planCheckDTO.innerWork, jdbcType=VARCHAR},");
                    //sql.append("a.processroute = #{planCheckDTO.processRoute, jdbcType=VARCHAR},");
                    //sql.append("a.longperiodpart = #{planCheckDTO.longPeriodPart, jdbcType=VARCHAR},");
                    //sql.append("a.deliverytype = #{planCheckDTO.deliveryType, jdbcType=VARCHAR},");
                    //sql.append("a.parttype = #{planCheckDTO.partType, jdbcType=VARCHAR},");
                    //sql.append("a.jfworkshop = #{planCheckDTO.jfWorkshop, jdbcType=VARCHAR},");
                    //sql.append(" a.jsworkshop = #{planCheckDTO.jsWorkshop, jdbcType=VARCHAR},");
                    //sql.append("a.deliveryorder = #{planCheckDTO.deliveryOrder, jdbcType=VARCHAR},");
                    //sql.append("a.matching = #{planCheckDTO.matching, jdbcType=VARCHAR},");
                    //sql.append("a.grouping_a = #{planCheckDTO.groupingA, jdbcType=VARCHAR},");
                    //sql.append("a.isexecutable = #{planCheckDTO.isExecutable, jdbcType=VARCHAR},");
                    //sql.append(" a.delivertotal = #{planCheckDTO.deliverTotal, jdbcType=VARCHAR},");
                    //sql.append("a.deliverstate = #{planCheckDTO.deliverState, jdbcType=VARCHAR},");
                    //sql.append("a.outprocess = #{planCheckDTO.outProcess, jdbcType=VARCHAR},");
                    //sql.append("a.equal = #{planCheckDTO.equal, jdbcType=VARCHAR},");
                    //sql.append("a.waixie = #{planCheckDTO.waixie, jdbcType=VARCHAR},");
                    //sql.append("a.waixiefactory = #{planCheckDTO.waixieFactory, jdbcType=VARCHAR},");
                    //sql.append("a.remarks1 = #{planCheckDTO.remarks1, jdbcType=VARCHAR},");
                    //sql.append("a.moreinfo = #{planCheckDTO.moreInfo, jdbcType=VARCHAR},");
                    //sql.append("a.endproduct = #{planCheckDTO.endProduct, jdbcType=VARCHAR},");
                    //sql.append("a.tools = #{planCheckDTO.tools, jdbcType=VARCHAR},");
                    //sql.append("a.equipment = #{planCheckDTO.equipment, jdbcType=VARCHAR},");
                    //sql.append("a.technicalquality = #{planCheckDTO.technicalQuality, jdbcType=VARCHAR},");
                    //sql.append("a.blanking = #{planCheckDTO.blanking, jdbcType=VARCHAR},");
                    //sql.append("a.outsourcing = #{planCheckDTO.outsourcing, jdbcType=VARCHAR},");
                    //sql.append("a.outcooperation = #{planCheckDTO.outCooperation, jdbcType=VARCHAR},");
                    //sql.append("a.ladong = #{planCheckDTO.ladong, jdbcType=VARCHAR},");
                    //sql.append("a.plangrade = #{planCheckDTO.planGrade, jdbcType=VARCHAR},");
                    //sql.append("a.issue = #{planCheckDTO.issue, jdbcType=VARCHAR},");
                    //sql.append("a.mesprogress = #{planCheckDTO.mesProgress, jdbcType=VARCHAR},");
                    //sql.append("a.state = #{planCheckDTO.state, jdbcType=VARCHAR},");
                    //sql.append("a.parentpartid = #{planCheckDTO.parentPartId, jdbcType=VARCHAR},");
                    //sql.append("a.affectedstation = #{planCheckDTO.affectedStation, jdbcType=VARCHAR} ");
                    //if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getChargePerson())) {
                    //    sql.append(", a.chargeperson = #{planCheckDTO.chargePerson, jdbcType=VARCHAR}");
                    //}
                    //if (planCheckDTO.getCheckTime() != null) {
                    //    sql.append(", a.checktime = #{planCheckDTO.checkTime, jdbcType=DATE}");
                    //}
                    //if (planCheckDTO.getPlanTime() != null){
                    //    sql.append(", a.plantime = #{planCheckDTO.planTime, jdbcType=DATE}");
                    //}
                    //if (planCheckDTO.getActualTime() != null){
                    //    sql.append(", a.actualtime = #{planCheckDTO.actualTime, jdbcType=DATE}");
                    //}
                    //if (planCheckDTO.getLadongchecktime() != null){
                    //    sql.append(", a.ladongchecktime = #{planCheckDTO.ladongchecktime, jdbcType=DATE}");
                    //}
                    //if (planCheckDTO.getDeliverTime() != null){
                    //    sql.append(", a.delivertime = #{planCheckDTO.deliverTime, jdbcType=DATE}");
                    //}
                    //if (planCheckDTO.getCompletionTime() != null){
                    //    sql.append(", a.completiontime = #{planCheckDTO.completionTime, jdbcType=DATE}");
                    //}
                    //if (planCheckDTO.getGdcheckTime() != null){
                    //    sql.append(", a.gdchecktime = #{planCheckDTO.gdcheckTime, jdbcType=DATE}");
                    //}
                    //if (planCheckDTO.getCheckTime2Date() != null){
                    //    sql.append(", a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm')");
                    //}
                    //if (planCheckDTO.getJxmlStock() != null){
                    //    sql.append(", a.jxmlstock = #{planCheckDTO.jxmlStock, jdbcType=INTEGER}");
                    //}
                    //if (planCheckDTO.getJxmlTotal() != null){
                    //    sql.append(", a.mlstocktotal = #{planCheckDTO.mlStockTotal, jdbcType=INTEGER}");
                    //}
                    //if (planCheckDTO.getBcpStockNum() != null){
                    //    sql.append(", a.bcpstocknum = #{planCheckDTO.bcpStockNum, jdbcType=INTEGER}");
                    //}
                    //if (planCheckDTO.getBcpStockTotal() != null){
                    //    sql.append(", a.bcpstocktotal = #{planCheckDTO.bcpStockTotal, jdbcType=INTEGER}");
                    //}
                    //if (planCheckDTO.getTempPlanNum() != null){
                    //    sql.append(", a.tempplannum = #{planCheckDTO.tempPlanNum, jdbcType=INTEGER}");
                    //}
                    //if (planCheckDTO.getJxmlTotal() != null){
                    //    sql.append(", a.jxmltotal = #{planCheckDTO.jxmlTotal, jdbcType=INTEGER},");
                    //}
                    //if (planCheckDTO.getJxbcpTotal() != null){
                    //    sql.append(", a.jxbcptotal = #{planCheckDTO.jxbcpTotal, jdbcType=INTEGER}");
                    //}
                    //if (planCheckDTO.getProductStock() != null){
                    //    sql.append(", a.productstock = #{planCheckDTO.productStock, jdbcType=INTEGER}");
                    //}
                    //sql.append(" where a.objuid = #{planCheckDTO.objuid, jdbcType=VARCHAR}");
                    UPDATE(sql.toString());
                    System.out.println("updateCheckSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    /**
     * 导出查询
     * @param planCheckDTO
     * @param page
     * @return
     */
    @SelectProvider(type = createSQL.class, method = "exportCheckSQL")
    List<PlanCheckExport> exportCheck(@Param("planCheckDTO") PlanCheckDTO planCheckDTO, Page<PlanCheckExport> page);
    public static class createSQL {
        public static String exportCheckSQL(@Param("planCheckDTO") PlanCheckDTO planCheckDTO, Page<PlanCheckExport> page){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" a.objuid,a.flag,replace(a.serialnumber, '-', '') as serialnumber,a.jx,a.matching,a.plangrade,");
                    sql.append(" a.delivertotal,a.partid,a.djnum,a.innerwork,a.processroute,a.grouping_a,a.jxmlstock,");
                    sql.append(" a.mlstocktotal,a.bcpstocknum,a.bcpstocktotal,a.jxmltotal,a.jxbcptotal,");
                    sql.append(" a.mesprogress,a.moreinfo,a.equal,a.issue,b.name,to_char(a.checktime, 'yyyy-mm-dd') as checktime,");
                    sql.append(" a.ladong,to_char(a.ladongchecktime, 'yyyy-mm-dd') as ladongchecktime,to_char(a.delivertime, 'yyyy-mm-dd') as delivertime,");
                    sql.append(" to_char(a.completiontime, 'yyyy-mm-dd') as completiontime,a.tempplannum,to_char(a.gdchecktime, 'yyyy-mm-dd') as gdchecktime,");
                    sql.append(" to_char(a.plantime, 'yyyy-mm-dd') as plantime,to_char(a.actualtime, 'yyyy-mm-dd') as actualtime,a.waixie,");
                    sql.append(" a.waixiefactory,a.deliverstate,a.outprocess,a.productstock,a.affectedstation,");
                    sql.append(" a.parttype,a.remarks1,a.isexecutable,a.jzjh,a.state,a.llworkshop,a.jfworkshop,a.jsworkshop");
                    sql.append(" from mes_plan_xiada a left join (select b.mes_plan_xiada_uid,wm_concat(c.name) as name from");
                    sql.append(" (select b.mes_plan_xiada_uid, b.stf_employee_uid from mes_plan_xiada_fuzeren b) b");
                    sql.append(" inner join (select c.objuid, c.name from stf_employee c) c on b.stf_employee_uid = c.objuid");
                    sql.append(" group by b.mes_plan_xiada_uid) b on a.objuid = b.mes_plan_xiada_uid");
                    //sql.append(" left join mes_gy_info_more c on a.planid = c.mes_gy_info_obj and a.serialnumber = c.serialnumber and a.checktime2 like c.nianyue || '%'");
                    sql.append(" where a.factory_id = #{planCheckDTO.factoryId, jdbcType=VARCHAR}");
                    if (planCheckDTO.getCheckTime2Date() != null){
                        if (planCheckDTO.getFactoryId().equals("16")){
                            sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm-dd') ");
                        } else {
                            sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') ");
                        }
                        //sql.append(" and a.checktime2 = to_char(#{planCheckDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getMatching())){
                        if ("空".equals(planCheckDTO.getMatching())) {
                            sql.append(" and a.matching is null ");
                        } else {
                            sql.append(" and a.matching = #{planCheckDTO.matching, jdbcType=VARCHAR} ");
                        }
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPartid())){
                        sql.append(" and a.partid = #{planCheckDTO.partid, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getInnerwork())){
                        sql.append(" and a.innerwork = #{planCheckDTO.innerwork, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPlangrade())){
                        sql.append(" and a.plangrade in ( ");
                        sql.append(planCheckDTO.getPlangrade());
                        sql.append(")");
                    }
                    //if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPlangrade())){
                    //    sql.append(" and a.plangrade = #{planCheckDTO.plangrade, jdbcType=VARCHAR} ");
                    //}
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getGroupingA())){
                        sql.append(" and a.grouping_a = #{planCheckDTO.groupingA, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getChargeperson())){
                        sql.append(" and b.name like '%' || #{planCheckDTO.chargeperson, jdbcType=VARCHAR} || '%'");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getIsexecutable())){
                        sql.append(" and a.isexecutable = #{planCheckDTO.isexecutable, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getJzjh())){
                        sql.append(" and a.jzjh = #{planCheckDTO.jzjh, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getWaixie())){
                        String a = planCheckDTO.getWaixie();
                        sql.append(" and a.waixie in ("+a+")");
                    }
                    if (planCheckDTO.getGdchecktime() != null){
                        sql.append(" and a.gdchecktime = #{planCheckDTO.gdchecktime}");
                    }
                    if (planCheckDTO.getPlantime1() != null){
                        sql.append(" and a.plantime >= to_date(to_char(#{planCheckDTO.plantime1}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (planCheckDTO.getPlantime2() != null){
                        sql.append(" and a.plantime <= to_date(to_char(#{planCheckDTO.plantime2}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getIsPlanTime())){
                        if (planCheckDTO.getIsPlanTime().equals("0")) {
                            sql.append(" and a.plantime is null");
                        }
                        if (planCheckDTO.getIsPlanTime().equals("1")) {
                            sql.append(" and a.plantime is not null");
                        }
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getParentpartid())){
                        sql.append(" and a.parentpartid = trim(upper(#{planCheckDTO.parentpartid, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getPartidLike())){
                        sql.append(" and a.partid like '%'||trim(upper(#{planCheckDTO.partidLike, jdbcType=VARCHAR}))||'%' ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getMoreinfo())){
                        sql.append(" and a.moreinfo like '%'||trim(#{planCheckDTO.moreinfo, jdbcType=VARCHAR})||'%' ");
                    }
                    if (planCheckDTO.getKexingColor() != null){
                        sql.append(" and a.kexing_color = #{planCheckDTO.kexingColor, jdbcType=INTEGER} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getSerialnumber())) {
                        sql.append(" and a.serialnumber like #{planCheckDTO.serialnumber, jdbcType=VARCHAR} || '%'");
                        if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getJx())) {
                            sql.append(" and a.jx = #{planCheckDTO.jx, jdbcType=VARCHAR}");
                        }
                    } else if (!StrUtil.isEmptyOrUndefined(planCheckDTO.getJx())) {
                        sql.append(" and a.jx in ( ");
                        sql.append(planCheckDTO.getJx());
                        sql.append(")");
                    }
                    sql.append(" order by a.jx, a.serialnumber");
                    SELECT(sql.toString());
                    System.out.println("queryCheckSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    /**
     * 点击mes进度查询制造记录信息
     * @param planCheckDTO
     * @return
     */
    @Select("select t.*, t.rowid from MES_ORDER_TRACE t \n" +
            " where t.jx = #{planCheckDTO.jx, jdbcType=VARCHAR}\n" +
            " and t.partid = #{planCheckDTO.partid, jdbcType=VARCHAR}\n" +
            " and t.jieshouchejian = #{planCheckDTO.jsworkshop, jdbcType=VARCHAR}\n" +
            " and t.factory_id = #{planCheckDTO.factoryId, jdbcType=VARCHAR}\n" +
            " and t.jfsx = #{planCheckDTO.deliveryorder, jdbcType=VARCHAR}\n" +
            " and t.partstate in ('0','2')\n" +
            " and t.create_date >= trunc(add_months(sysdate,-24),'yyyy')\n" +
            " order by t.create_date desc")
    List<MesOrderTraceTiaoMaVO> mesProgress(@Param("planCheckDTO") PlanCheckDTO planCheckDTO);

    // 计划核对
    @Update("update mes_plan_xiada a set " +
            " a.planstate = 1," +
            " a.checksuid = #{checksUid, jdbcType=VARCHAR}," +
            " a.checkstime = sysdate" +
            " where a.objuid = #{objuid, jdbcType=VARCHAR}")
    int checkPlan(@Param("objuid") String objuid, @Param("checksUid") String checksUid);

    // 工段计划查询
    @SelectProvider(type = createSQL3.class, method = "queryGDPlanSQL")
    List<GongDuanPlanDTO> queryGDPlan(@Param("gongDuanPlanDTO") GongDuanPlanDTO gongDuanPlanDTO, Page<GongDuanPlanDTO> page);
    public static class createSQL3 {
        public static String queryGDPlanSQL(@Param("gongDuanPlanDTO") GongDuanPlanDTO gongDuanPlanDTO, Page<GongDuanPlanDTO> page){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" a.objuid, a.flag, a.jx, a.partid, a.djnum,a.innerwork, a.longperiodpart, a.processroute,");
                    sql.append(" a.deliverytype, a.parttype, a.jfworkshop, a.jsworkshop, a.deliveryorder, a.matching,");
                    sql.append(" a.grouping_a, a.checktime, a.isexecutable, a.delivertotal, a.deliverstate, a.jxmlstock,");
                    sql.append(" a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal, a.mesprogress, a.outprocess, a.equal,");
                    sql.append(" a.waixie, a.waixiefactory, a.tempplannum, a.remarks1, a.moreinfo,  a.chargeperson,");
                    sql.append(" a.endproduct, a.tools,  a.equipment, a.technicalquality, a.blanking,  a.outsourcing,");
                    sql.append(" a.outcooperation, a.plantime, a.actualtime, a.ladong, a.ladongchecktime, a.plangrade,");
                    sql.append(" a.jxmltotal, a.jxbcptotal, a.issue, a.delivertime, a.completiontime, a.gdchecktime,");
                    sql.append(" replace(a.serialnumber, '-', '') as serialnumber, a.productstock, a.affectedstation,");
                    sql.append(" a.planstate, a.factory_id, a.putnum, a.state, a.checktime2, a.parentpartid, a.jzjh, a.lltotal,");
                    //sql.append(" to_date(a.checktime2, 'yyyy-mm') as checkTime2date, b.name, c.isexecutable isexecutable2");
                    if (gongDuanPlanDTO.getFactoryId().equals("16")){
                        sql.append(" to_date(a.checktime2, 'yyyy-mm-dd') as checkTime2date,");
                    } else {
                        sql.append(" to_date(a.checktime2, 'yyyy-mm') as checkTime2date,");
                    }
                    sql.append(" b.name, a.kexing_color");
                    sql.append(" from mes_plan_xiada a left join (select b.mes_plan_xiada_uid,wm_concat(c.name) as name from");
                    sql.append(" (select b.mes_plan_xiada_uid, b.stf_employee_uid from mes_plan_xiada_fuzeren b) b");
                    sql.append(" inner join (select c.objuid, c.name from stf_employee c) c on b.stf_employee_uid = c.objuid");
                    sql.append(" group by b.mes_plan_xiada_uid) b on a.objuid = b.mes_plan_xiada_uid");
                    //sql.append(" left join mes_gy_info_more c on a.planid = c.mes_gy_info_obj and a.serialnumber = c.serialnumber and a.checktime2 like c.nianyue || '%'");
                    sql.append(" where a.factory_id = #{gongDuanPlanDTO.factoryId, jdbcType=VARCHAR}");
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getJx())){
                        sql.append(" and a.jx = trim(upper(#{gongDuanPlanDTO.jx, jdbcType=VARCHAR})) ");
                    }
                    if (gongDuanPlanDTO.getCheckTime2Date() != null){
                        if (gongDuanPlanDTO.getFactoryId().equals("16")){
                            sql.append(" and a.checktime2 = to_char(#{gongDuanPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm-dd') ");
                        } else {
                            sql.append(" and a.checktime2 = to_char(#{gongDuanPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') ");
                        }
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getInnerwork())) {
                        switch (gongDuanPlanDTO.getInnerwork()) {
                            case "1":
                            case "2":
                            case "3":
                                sql.append(" and a.innerwork = #{gongDuanPlanDTO.innerwork, jdbcType=VARCHAR} ");
                                break;
                            case "0":
                                sql.append(" and a.waixie like '%外协%' ");
                                break;
                            default:
                                break;
                        }
                    }
                    if (gongDuanPlanDTO.getPlantime1() != null){
                        sql.append(" and a.plantime >= to_date(to_char(#{gongDuanPlanDTO.plantime1}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (gongDuanPlanDTO.getPlantime2() != null){
                        sql.append(" and a.plantime <= to_date(to_char(#{gongDuanPlanDTO.plantime2}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getMatching())){
                        sql.append(" and a.matching = #{gongDuanPlanDTO.matching, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getPartid())){
                        sql.append(" and a.partid = #{gongDuanPlanDTO.partid, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getPlangrade())){
                        sql.append(" and a.plangrade in ( ");
                        sql.append(gongDuanPlanDTO.getPlangrade());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getGroupingA())){
                        sql.append(" and a.grouping_a = #{gongDuanPlanDTO.groupingA, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getChargeperson())){
                        sql.append(" and b.name like '%' || #{gongDuanPlanDTO.chargeperson, jdbcType=VARCHAR} || '%'");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getIsexecutable())){
                        sql.append(" and a.isexecutable = #{gongDuanPlanDTO.isexecutable, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getJzjh())){
                        sql.append(" and a.jzjh = #{gongDuanPlanDTO.jzjh, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getWaixie())){
                        String a = gongDuanPlanDTO.getWaixie();
                        sql.append(" and a.waixie in ("+a+")");
                    }
                    if (gongDuanPlanDTO.getGdchecktime() != null){
                        sql.append(" and a.gdchecktime = #{gongDuanPlanDTO.gdchecktime}");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getPartidLike())){
                        sql.append(" and a.partid like '%'||trim(upper(#{gongDuanPlanDTO.partidLike, jdbcType=VARCHAR}))||'%' ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getMoreinfo())){
                        sql.append(" and a.moreinfo like '%'||trim(#{gongDuanPlanDTO.moreinfo, jdbcType=VARCHAR})||'%' ");
                    }
                    sql.append(" order by a.jx, a.serialnumber");
                    SELECT(sql.toString());
                    System.out.println("queryGDPlanSQL:" + sql.toString());
                }
            }.toString();
        }
    }
    // 工段计划导出
    @SelectProvider(type = createSQL4.class, method = "exportGDPlanSQL")
    List<GongDuanPlanExport> exportGDPlan(@Param("gongDuanPlanDTO") GongDuanPlanDTO gongDuanPlanDTO, Page<GongDuanPlanExport> page);
    public static class createSQL4 {
        public static String exportGDPlanSQL(@Param("gongDuanPlanDTO") GongDuanPlanDTO gongDuanPlanDTO, Page<GongDuanPlanExport> page){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" a.objuid, a.flag, a.jx, a.partid, a.djnum,a.innerwork, a.longperiodpart, a.processroute,");
                    sql.append(" a.deliverytype, a.parttype, a.jfworkshop, a.jsworkshop, a.deliveryorder, a.matching,");
                    sql.append(" a.grouping_a, to_char(a.checktime, 'yyyy-mm-dd') as checktime, a.isexecutable, a.delivertotal, a.deliverstate, a.jxmlstock,");
                    sql.append(" a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal, a.mesprogress, a.outprocess, a.equal,");
                    sql.append(" a.waixie, a.waixiefactory, a.tempplannum, a.remarks1, a.moreinfo,  a.chargeperson,");
                    sql.append(" a.endproduct, a.tools,  a.equipment, a.technicalquality, a.blanking,  a.outsourcing,");
                    sql.append(" a.outcooperation, to_char(a.plantime, 'yyyy-mm-dd') as plantime, to_char(a.actualtime, 'yyyy-mm-dd') as actualtime, a.ladong, to_char(a.ladongchecktime, 'yyyy-mm-dd') as ladongchecktime, a.plangrade,");
                    sql.append(" a.jxmltotal, a.jxbcptotal, a.issue, to_char(a.delivertime, 'yyyy-mm-dd') as delivertime, to_char(a.completiontime, 'yyyy-mm-dd') as completiontime, to_char(a.gdchecktime, 'yyyy-mm-dd') as gdchecktime,");
                    sql.append(" replace(a.serialnumber, '-', '') as serialnumber, a.productstock, a.affectedstation,");
                    sql.append(" a.planstate, a.factory_id, a.putnum, a.state, a.checktime2, a.parentpartid, a.jzjh, a.lltotal,");
                    if (gongDuanPlanDTO.getFactoryId().equals("16")){
                        sql.append(" to_date(a.checktime2, 'yyyy-mm-dd') as checkTime2date,");
                    } else {
                        sql.append(" to_date(a.checktime2, 'yyyy-mm') as checkTime2date,");
                    }
                    sql.append(" b.name");
                    sql.append("  from mes_plan_xiada a left join (select b.mes_plan_xiada_uid,wm_concat(c.name) as name from");
                    sql.append(" (select b.mes_plan_xiada_uid, b.stf_employee_uid from mes_plan_xiada_fuzeren b) b");
                    sql.append(" inner join (select c.objuid, c.name from stf_employee c) c on b.stf_employee_uid = c.objuid");
                    sql.append(" group by b.mes_plan_xiada_uid) b on a.objuid = b.mes_plan_xiada_uid");
                    sql.append(" where a.factory_id = #{gongDuanPlanDTO.factoryId, jdbcType=VARCHAR}");
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getJx())){
                        sql.append(" and a.jx = trim(upper(#{gongDuanPlanDTO.jx, jdbcType=VARCHAR})) ");
                    }
                    if (gongDuanPlanDTO.getCheckTime2Date() != null){
                        if (gongDuanPlanDTO.getFactoryId().equals("16")){
                            sql.append(" and a.checktime2 = to_char(#{gongDuanPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm-dd') ");
                        } else {
                        sql.append(" and a.checktime2 = to_char(#{gongDuanPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm') ");
                        }
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getInnerwork())) {
                        switch (gongDuanPlanDTO.getInnerwork()) {
                            case "1":
                            case "2":
                            case "3":
                                sql.append(" and a.innerwork = #{gongDuanPlanDTO.innerwork, jdbcType=VARCHAR} ");
                                break;
                            case "0":
                                sql.append(" and a.waixie like '%外协%' ");
                                break;
                            default:
                                break;
                        }
                    }
                    if (gongDuanPlanDTO.getPlantime1() != null){
                        sql.append(" and a.plantime >= to_date(to_char(#{gongDuanPlanDTO.plantime1}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (gongDuanPlanDTO.getPlantime2() != null){
                        sql.append(" and a.plantime <= to_date(to_char(#{gongDuanPlanDTO.plantime2}, 'yyyy-mm-dd'), 'yyyy-mm-dd')");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getMatching())){
                        sql.append(" and a.matching = #{gongDuanPlanDTO.matching, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getPartid())){
                        sql.append(" and a.partid = #{gongDuanPlanDTO.partid, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getPlangrade())){
                        sql.append(" and a.plangrade in ( ");
                        sql.append(gongDuanPlanDTO.getPlangrade());
                        sql.append(")");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getGroupingA())){
                        sql.append(" and a.grouping_a = #{gongDuanPlanDTO.groupingA, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getChargeperson())){
                        sql.append(" and b.name like '%' || #{gongDuanPlanDTO.chargeperson, jdbcType=VARCHAR} || '%'");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getIsexecutable())){
                        sql.append(" and a.isexecutable = #{gongDuanPlanDTO.isexecutable, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getJzjh())){
                        sql.append(" and a.jzjh = #{gongDuanPlanDTO.jzjh, jdbcType=VARCHAR} ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getWaixie())){
                        if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getWaixie())){
                            String a = gongDuanPlanDTO.getWaixie();
                            sql.append(" and a.waixie in ("+a+")");
                        }
                    }
                    if (gongDuanPlanDTO.getGdchecktime() != null){
                        sql.append(" and a.gdchecktime = #{gongDuanPlanDTO.gdchecktime}");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getPartidLike())){
                        sql.append(" and a.partid like '%'||trim(upper(#{gongDuanPlanDTO.partidLike, jdbcType=VARCHAR}))||'%' ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(gongDuanPlanDTO.getMoreinfo())){
                        sql.append(" and a.moreinfo like '%'||trim(#{gongDuanPlanDTO.moreinfo, jdbcType=VARCHAR})||'%' ");
                    }
                    sql.append(" order by a.jx, a.serialnumber");
                    SELECT(sql.toString());
                    System.out.println("exportGDPlanSQL:" + sql.toString());
                }
            }.toString();
        }
    }

}
